package com.qike.huadongexcel.service.impl;

import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import com.qike.huadongexcel.controller.ResultListVo;
import com.qike.huadongexcel.dto.AddProjectDto;
import com.qike.huadongexcel.dto.DelProjectDto;
import com.qike.huadongexcel.dto.LoginDto;
import com.qike.huadongexcel.dto.ResultSave;
import com.qike.huadongexcel.enums.StatusEnums;
import com.qike.huadongexcel.excelTools.FileTools;
import com.qike.huadongexcel.excelTools.JacobExcelTool;
import com.qike.huadongexcel.excelTools.TokenUtil;
import com.qike.huadongexcel.mapper.*;
import com.qike.huadongexcel.pojo.*;
import com.qike.huadongexcel.service.ExcelService;
import com.qike.huadongexcel.vo.DBFileListVo;
import com.qike.huadongexcel.vo.LoginVo;
import com.qike.huadongexcel.vo.ProjectListVo;
import com.qike.huadongexcel.vo.UserVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private ExcelUserMapper excelUserMapper;
    @Autowired
    private ExcelProjectUserMapper excelProjectUserMapper;
    @Autowired
    private ExcelProjectMapper excelProjectMapper;
    @Autowired
    private ExcelResultMapper excelResultMapper;
    @Autowired
    private ExcelLogMapper excelLogMapper;
    @Autowired
    private TokenService tokenService;

    @Value("${file.db}")
    private String bdFile;
    @Value("${file.target}")
    private String targetFile;

    @Override
    public LoginVo login(LoginDto loginDto) throws Exception {
        ExcelUserExample excelUserExample = new ExcelUserExample();
        excelUserExample.createCriteria().andUserNameEqualTo(loginDto.getUserName()).andPassWordEqualTo(loginDto.getPassWord()).andStatusEqualTo(StatusEnums.NO.getEcode());
        List<ExcelUser> excelUsers = excelUserMapper.selectByExample(excelUserExample);
        if (excelUsers == null || excelUsers.size() == 0) {
            throw new Exception("用户名密码错误");
        }
        LoginVo vo = new LoginVo();
        vo.setName(excelUsers.get(0).getName());
        vo.setUid( excelUsers.get(0).getId());
        vo.setToken(tokenService.getToken(excelUsers.get(0)));
        return vo;
    }

    /**
     * 我创建的项目
     *
     * @param
     * @return
     */
    @Override
    public List<ProjectListVo> myProject() {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"打开项目列表");
        List<ProjectListVo> listVos = new ArrayList<>();
        ExcelProjectUserExample projectUserExample = new ExcelProjectUserExample();
        projectUserExample.createCriteria().andUserIdEqualTo(id);
        List<ExcelProjectUser> excelProjectUsers = excelProjectUserMapper.selectByExample(projectUserExample);
        if (excelProjectUsers.size() > 0) {
            for (ExcelProjectUser excelProjectUser : excelProjectUsers) {
                ProjectListVo vo = new ProjectListVo();
                ExcelProject excelProject = excelProjectMapper.selectByPrimaryKey(excelProjectUser.getProjectId());
                vo.setId(excelProject.getId());
                vo.setProjectName(excelProject.getProjectName());
                vo.setPath(excelProject.getUrl());
                vo.setPhoto(excelProject.getPhoto());
                listVos.add(vo);
            }
        }
        return listVos;
    }

    /**
     * 添加项目
     *
     * @param addProjectDto
     */
    @Override
    public void addProject(AddProjectDto addProjectDto) throws Exception {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"添加项目"+addProjectDto.getProjectName());
        //创建这个项目名的文件夹 文件夹名= 用户id+项目名
        //把模板文件复制进这个文件夹里的template
        String fileName = targetFile + id.toString() + addProjectDto.getProjectName();
        //判断是否有重名
        ExcelProjectExample excelProjectExample = new ExcelProjectExample();
        excelProjectExample.createCriteria().andProjectNameEqualTo(fileName).andStatusEqualTo(StatusEnums.YES.getEcode());
        List<ExcelProject> excelProjects = excelProjectMapper.selectByExample(excelProjectExample);
        if (excelProjects.size()>0) {
            throw new Exception("不能创建同名项目");
        }
        FileTools.copyDir(targetFile, fileName);

        //关联关系进数据库
        ExcelProject project = new ExcelProject();
        project.setAddTime(new Date());
        project.setPhoto("");
        project.setUrl(fileName);
        project.setProjectName(id.toString() + addProjectDto.getProjectName());
        project.setStatus(StatusEnums.YES.getEcode());
        excelProjectMapper.insert(project);
        ExcelProjectUser projectUser = new ExcelProjectUser();
        projectUser.setIsCreate(StatusEnums.YES.getEcode());
        projectUser.setProjectId(project.getId());
        projectUser.setUserId(id);
        excelProjectUserMapper.insert(projectUser);
        if (addProjectDto.getUids()!=null&&addProjectDto.getUids().size()>0){
            for (Integer uid : addProjectDto.getUids()) {
                ExcelProjectUser projectUserMember = new ExcelProjectUser();
                projectUserMember.setIsCreate(StatusEnums.NO.getEcode());
                projectUserMember.setProjectId(project.getId());
                projectUserMember.setUserId(uid);
                excelProjectUserMapper.insert(projectUserMember);
            }
        }


    }

    /**
     * 删除项目
     *
     * @param delProjectDto
     */
    @Override
    public void delProject(DelProjectDto delProjectDto) throws Exception {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"删除项目"+delProjectDto.getProjectId());
        ExcelProjectUserExample excelUserExample = new ExcelProjectUserExample();
        excelUserExample.createCriteria().andProjectIdEqualTo(delProjectDto.getProjectId()).andIsCreateEqualTo(StatusEnums.YES.getEcode()).andUserIdEqualTo(id);
        List<ExcelProjectUser> excelProjectUsers = excelProjectUserMapper.selectByExample(excelUserExample);
        if (excelProjectUsers.size()>0){
            excelUserExample.clear();
            excelUserExample.createCriteria().andProjectIdEqualTo(delProjectDto.getProjectId());
            excelProjectUserMapper.deleteByExample(excelUserExample);
            excelProjectMapper.deleteByPrimaryKey(delProjectDto.getProjectId());
        }else {
            throw new Exception("无权删除");
        }

    }

    /**
     * 成员列表
     *
     * @param projectId
     * @return
     */
    @Override
    public List<UserVo> userList( Integer projectId) {
        Integer id = TokenUtil.getTokenUserId();
        List<UserVo> userList = new ArrayList<>();
        //查询项目中已经有的成员
        Map<Integer, ExcelProjectUser> projectUser = new HashMap();
        if (projectId!=null){
            ExcelProjectUserExample projectUserExample = new ExcelProjectUserExample();
            projectUserExample.createCriteria().andProjectIdEqualTo(projectId).andIsCreateEqualTo(StatusEnums.NO.getEcode());
            List<ExcelProjectUser> excelProjectUsers = excelProjectUserMapper.selectByExample(projectUserExample);
            if (excelProjectUsers.size() > 0) {
                for (ExcelProjectUser excelProjectUser : excelProjectUsers) {
                    projectUser.put(excelProjectUser.getUserId(), excelProjectUser);
                }
            }
        }
        ExcelUserExample userExample = new ExcelUserExample();
        userExample.createCriteria().andIdNotEqualTo(id);
        List<ExcelUser> excelUsers = excelUserMapper.selectByExample(userExample);
        if (excelUsers.size() > 0) {
            for (ExcelUser excelUser : excelUsers) {
                UserVo userVo = new UserVo();
                userVo.setName(excelUser.getName());
                userVo.setUid(excelUser.getId());
                if (projectUser.get(excelUser.getId()) != null) {
                    userVo.setCheck(true);
                } else {
                    userVo.setCheck(false);
                }
                userList.add(userVo);

            }
        }

        return userList;
    }

    /**
     * 下载数据库表
     *
     */
    @Override
    public void DBFileDownLoad(String path, String fileName, HttpServletResponse response) throws FileNotFoundException {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"下载数据库列表路径"+path);
        try {
            String[] split = path.split(",");
            if (split.length>0){
                List<File>  fileList = new ArrayList<>();
                for (String s : split) {
                    File file = new File(s);
                    fileList.add(file);
                }
                zipd(bdFile+"/数据库模板.zip",fileList,response);
            }else {
                // path是指欲下载的文件的路径。
                File file = new File(path);
                // 取得文件名。
                String filename = file.getName();
                // 取得文件的后缀名。
                String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();

                // 以流的形式下载文件。
                InputStream fis = new BufferedInputStream(new FileInputStream(path));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                // 清空response
                response.reset();
                // 设置response的Header
                response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
                response.addHeader("Content-Length", "" + file.length());
                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/ms-excel;charset=utf-8");
                response.setCharacterEncoding("UTF-8");
                toClient.write(buffer);
                toClient.flush();
                toClient.close();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 上传数据库表
     *
     * @param file
     */
    @Override
    public void DBFileUpLoad(MultipartFile file,Integer projectId) throws Exception {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"上传数据库列表项目id"+projectId);
        //校验文件名
        ExcelProject project = excelProjectMapper.selectByPrimaryKey(projectId);
        String path = project.getUrl() + "/template/";
        if (file.isEmpty()) {
            throw new Exception("上传失败，请选择文件") ;
        }

        String fileName = file.getOriginalFilename();
        String filePath = path;
        File dest = new File(filePath + fileName);
        try {
            file.transferTo(dest);
        } catch (IOException e) {
            throw new Exception("上传失败") ;
        }

    }

    /**
     * 数据库表列表
     *
     * @param projectId
     * @return
     */
    @Override
    public List<DBFileListVo> DBFileList(Integer projectId) throws IOException {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"查询数据库列表项目id"+projectId);
        List<DBFileListVo> vos = new ArrayList<>();
        ExcelProject project = excelProjectMapper.selectByPrimaryKey(projectId);
        String url = project.getUrl();
        List<File> readfile = FileTools.readfile(url + "/template");
        for (File file : readfile) {
            if (!file.getName().contains("配电箱系统图")){
                DBFileListVo vo = new DBFileListVo();
                vo.setFileName(file.getName());
                vo.setPath(file.getPath());
                vo.setMins(FileTools.getFileCreateTime(file.getPath()));
                vos.add(vo);
            }

        }

        return vos;
    }

    /**
     * 生成结果运行宏
     *
     * @param projectId
     */
    @Override
    public void excelStart(Integer projectId) throws IOException {
        Integer id = TokenUtil.getTokenUserId();
        ExcelProject project = excelProjectMapper.selectByPrimaryKey(projectId);
        List<ResultListVo> vos = new ArrayList<>();
        String path = project.getUrl() + "/resultTemplate";
        List<File> readfile = FileTools.readfile(path);
        for (File file : readfile) {
            JacobExcelTool tool = new JacobExcelTool();
            tool.getExcel(file.getPath(),true,false);
            Dispatch sheet = tool.getSheetByName("配电箱系统图");
            Variant num = tool.getValue("B1", sheet);
            if (num.toString().equals("输出")){
                ResultListVo vo = new ResultListVo();
                vo.setFileName(file.getName());
                vo.setPath(file.getPath());
                vos.add(vo);
            }
        }
//        return vos;

    }

    /**
     * 结果文件列表
     * @param projectId
     * @return
     */
    @Override
    public List<ResultListVo> resultList( Integer projectId) throws IOException {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"查询结果列表项目id"+projectId);
        List<ResultListVo> vos = null;
        JacobExcelTool tool = null;
        List<String> cloums = new ArrayList<>();
        cloums.add("Y");
        cloums.add("Z");
        cloums.add("AA");
        cloums.add("AB");
        cloums.add("AC");
        cloums.add("AD");
        cloums.add("AE");
        cloums.add("AF");
        cloums.add("AG");
        cloums.add("AH");
        cloums.add("AI");
        cloums.add("AJ");
        cloums.add("AK");
        cloums.add("AL");
        cloums.add("AM");
        cloums.add("AN");
        cloums.add("AO");
        cloums.add("AP");
        cloums.add("AQ");
        cloums.add("AR");

        Map<String,String> fileNames = new HashMap<>();
        try {
             tool = new JacobExcelTool();
            ExcelProject project = excelProjectMapper.selectByPrimaryKey(projectId);
            vos = new ArrayList<>();
            String path = project.getUrl() + "/template";
            List<File> readfile = FileTools.readfile(path);
            for (File file : readfile) {
                //打开数据库模板
                if (file.getName().contains("配电箱统计数据库")){
                    tool.OpenExcel(file.getPath(),false,false);
                    Dispatch sheet = tool.getSheetByName("配电箱信息核对");
                    for (String cloum : cloums) {
                        for (int i = 2; i <=51; i++) {
                            Variant num = tool.getValue(cloum+i, sheet);
                            if (!num.toString().equals("null")){
                                Variant projectName = tool.getValue(cloum+1, sheet);
                                fileNames.put(projectName.toString(),projectName.toString());
                            }
                        }
                    }
                }
            }
            Set<String> fileSet = fileNames.keySet();
            for (String s : fileSet) {
                ResultListVo vo = new ResultListVo();
                vo.setFileName(s);
                vo.setPath(path+"/"+s+".xlsm");
                ExcelResultExample excelResultExample = new ExcelResultExample();
                excelResultExample.createCriteria().andProjectIdEqualTo(projectId).andFileNameEqualTo(s);
                List<ExcelResult> excelResults = excelResultMapper.selectByExample(excelResultExample);
                if (excelResults.size()>0){
                    vo.setSave(StatusEnums.YES.getEcode());
                }else {
                    vo.setSave(StatusEnums.NO.getEcode());
                }
                vos.add(vo);
            }
            System.out.println(fileNames);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            tool.CloseExcel(true, true);
        }
        return vos;
    }

    /**
     * 结果文件下载
     * @param filePath
     * @param fileName
     * @param response
     */
    @Override
    public void resultDownLoad(String filePath, String fileName, HttpServletResponse response) throws IOException {
        Integer id = TokenUtil.getTokenUserId();
        log(id,"结果文件下载路径"+filePath);
        List<File>  fileList = new ArrayList<>();
        List<String>  filePaths = new ArrayList<>();
        try {
        String[] split = filePath.split(",");
        if (split.length>0){
            for (String s : split) {
                String newPath = s.substring(0,filePath.lastIndexOf("."))+"copy"+".xlsm";
                filePaths.add(newPath);
                FileTools.copyFile(s,newPath);
                File file = new File(newPath);
                FileTools.runHong(newPath,true,false,"隐藏公式及清除多余数据");
                fileList.add(file);
            }
            zipd(bdFile+"/结果列表.zip",fileList,response);
        }
//        String newPath = filePath.substring(0,filePath.lastIndexOf("."))+"copy"+".xlsm";
//
//            // path是指欲下载的文件的路径。
//            FileTools.copyFile(filePath,newPath);
//            File file = new File(newPath);
//            //todo 运行消除公式的宏
//            FileTools.runHong(newPath,true,false,"隐藏公式及清除多余数据");
//            // 取得文件名。
//            String filename = file.getName();
//            // 取得文件的后缀名。
//            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
//
//            // 以流的形式下载文件。
//            InputStream fis = new BufferedInputStream(new FileInputStream(newPath));
//            byte[] buffer = new byte[fis.available()];
//            fis.read(buffer);
//            fis.close();
//            // 清空response
//            response.reset();
//            // 设置response的Header
//            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
//            response.addHeader("Content-Length", "" + file.length());
//            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
//            response.setContentType("application/ms-excel;charset=utf-8");
//            response.setCharacterEncoding("UTF-8");
//            toClient.write(buffer);
//            toClient.flush();
//            toClient.close();

        } catch (IOException ex) {
            ex.printStackTrace();
        }finally {
            //todo 删除文件
            for (String path : filePaths) {
                FileTools.deleteFile(path);
            }


        }
    }

    /**
     * 运行暂存 配电箱修改检测复位 宏
     * @param resultSave
     */
    @Override
    public void resultSave(ResultSave resultSave) throws Exception {
        JacobExcelTool jacobExcelTool = null;
        try {
            Integer id = TokenUtil.getTokenUserId();
            log(id,"运行暂存"+resultSave.getFilePath());
            //todo 运行消除公式的宏
            //先打开数据库模板
             jacobExcelTool = new JacobExcelTool();
            String dbFile1 = resultSave.getFilePath().substring(0,resultSave.getFilePath().lastIndexOf("/"))+"基础原则数据库.xlsx";
            String dbFile2 = resultSave.getFilePath().substring(0,resultSave.getFilePath().lastIndexOf("/"))+"配电箱统计数据库.xlsm";
            jacobExcelTool.OpenExcel(dbFile1,true,false);
            jacobExcelTool.OpenExcel(dbFile2,true,false);
            String[] split = resultSave.getFilePath().split(",");
            for (String s : split) {
                FileTools.runHong(resultSave.getFilePath(),false,false,"配电箱修改检测复位");
                //保存关系 已经暂存的文件
                File file = new File(s);
                ExcelResult excelResult = new ExcelResult();
                excelResult.setFileName(file.getName());
                excelResult.setFilePath(s);
                excelResult.setProjectId(resultSave.getProjectId());
                excelResult.setSave(StatusEnums.YES.getEcode());
                ExcelResultExample excelResultExample = new ExcelResultExample();
                excelResultExample.createCriteria().andFileNameEqualTo(resultSave.getFileName()).andProjectIdEqualTo(resultSave.getProjectId());
                List<ExcelResult> excelResults = excelResultMapper.selectByExample(excelResultExample);
                if (excelResults.size()>0){
                    throw new Exception("已经保存了");
                }
                excelResultMapper.insert(excelResult);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            jacobExcelTool.CloseExcel(true,true);
        }
    }



    public void log(Integer uid,String content){
        ExcelLog excelLog = new ExcelLog();
        excelLog.setUserId(uid);
        excelLog.setAction(content);
        excelLog.setAddTime(new Date());
        excelLogMapper.insert(excelLog);
    }

    //多个文件，压缩成zip后下载
    public void downloadMoreFile(HttpServletResponse response) {


//        String test1= "D:\test\ce\1.txt";
//        String test2= "D:\test\ce\2.txt";

//        File tfile= new File(test1);
//        File cfile= new File(test2);
//
//        List<File> files = new ArrayList<>();
//        files.add(tfile);
//        files.add(cfile);
//        if (tfile.exists() && cfile.exists()) {
//
//            String zipTmp = "D:\test\ce\1.zip";
//            zipd(zipTmp,files,response);
//
//
//        }
    }

    public void zipd(String zipTmp,List<File> files,HttpServletResponse response){
        File zipTmpFile = new File(zipTmp);
        try {
            if (zipTmpFile.exists()) {
                zipTmpFile.delete();
            }
            zipTmpFile.createNewFile();

            response.reset();
            // 创建文件输出流
            FileOutputStream fous = new FileOutputStream(zipTmpFile);
            ZipOutputStream zipOut = new ZipOutputStream(fous);
            zipFile(files, zipOut);
            zipOut.close();
            fous.close();
            downloadZip(zipTmpFile, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    //files打成压缩包
    public void zipFile(List files, ZipOutputStream outputStream) {
        int size = files.size();
        for (int i = 0; i < size; i++) {
            File file = (File) files.get(i);
            zipFile(file, outputStream);
        }
    }


    public void zipFile(File inputFile, ZipOutputStream ouputStream) {
        try {
            if (inputFile.exists()) {
                if (inputFile.isFile()) {
                    FileInputStream IN = new FileInputStream(inputFile);
                    BufferedInputStream bins = new BufferedInputStream(IN, 512);
                    ZipEntry entry = new ZipEntry(inputFile.getName());
                    ouputStream.putNextEntry(entry);

                    int nNumber;
                    byte[] buffer = new byte[512];
                    while ((nNumber = bins.read(buffer)) != -1) {
                        ouputStream.write(buffer, 0, nNumber);
                    }

                    bins.close();
                    IN.close();
                } else {
                    try {
                        File[] files = inputFile.listFiles();
                        for (int i = 0; i < files.length; i++) {
                            zipFile(files[i], ouputStream);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static HttpServletResponse downloadZip(File file, HttpServletResponse response) {
        if (file.exists() == false) {
            System.out.println("待压缩的文件目录：" + file + "不存在.");
        } else {
            try {
                // 以流的形式下载文件。
                InputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                // 清空response
                response.reset();

                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
                response.setContentType("application/octet-stream");

                // 如果输出的是中文名的文件，在此处就要用URLEncoder.encode方法进行处理
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + new String(file.getName().getBytes("GB2312"), "ISO8859-1"));
                toClient.write(buffer);
                toClient.flush();
                toClient.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                try {
                    File f = new File(file.getPath());
                    f.delete();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return response;
    }
}
